# -*- coding: utf-8 -*-

class SqliteSql:
    checkSkuCode = "select count(1) from PS_C_SKU where SKU_ECODE='%s'"  # 根据条码检索商品是否存在
    checkProCode = "select count(1) from PS_C_PRO where ECODE='%s'"  # 根据款号检索商品是否存在
    checkProGbcode = "select count(1) from PS_C_PRO where GBCODE='%s'"  # 根据国际码检索商品是否存在
    checkPro = "select count(1) from ps_c_pro where ecode='%s' "  # 根据款号检索该款是否存在
    getSku = """select a.id as BarCodeId,a.sku_ecode as BarCode,b.id as productid,b.cp_c_distrib_id as distribid,c.ecode as distribidcode,c.ename as distribidname,
           b.ps_c_brand_id as brandid,d.ecode as brandcode,d.ename as brandname, b.ecode as productcode,b.ename as productname,ifnull(b.txtdim3,''),ifnull(b.pricelist,0),
           ifnull(b.pricelower,0),
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.proyear and tbdim.ad_procolumn_name='PROYEAR' and tbpro.id=a.ps_c_pro_id ),'') as prouear,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.numdim6 and tbdim.ad_procolumn_name='NUMDIM6'  and tbpro.id=a.ps_c_pro_id ),'') as numdim6,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.largeclass and tbdim.ad_procolumn_name='LARGECLASS'  and tbpro.id=a.ps_c_pro_id ),'') as largeclass,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.sex and tbdim.ad_procolumn_name='SEX'  and tbpro.id=a.ps_c_pro_id ),'') as sex,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.prosea and tbdim.ad_procolumn_name='PROSEA'  and tbpro.id=a.ps_c_pro_id ),'') as prosea,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.promotiontype and tbdim.ad_procolumn_name='PROMOTIONTYPE'  and tbpro.id=a.ps_c_pro_id ),'') as promotiontype,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.numdim5 and tbdim.ad_procolumn_name='NUMDIM5'  and tbpro.id=a.ps_c_pro_id ),'') as numdim5,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.series and tbdim.ad_procolumn_name='SERIES'  and tbpro.id=a.ps_c_pro_id ),'') as series,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.proband and tbdim.ad_procolumn_name='PROBAND'  and tbpro.id=a.ps_c_pro_id ),'') as proband,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.pronature and tbdim.ad_procolumn_name='PRONATURE'  and tbpro.id=a.ps_c_pro_id ),'') as pronature,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.numdim8 and tbdim.ad_procolumn_name='NUMDIM8'  and tbpro.id=a.ps_c_pro_id ),'') as numdim8,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.priceband and tbdim.ad_procolumn_name='PRICEBAND'  and tbpro.id=a.ps_c_pro_id ),'') as priceband,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.priceband and tbdim.ad_procolumn_name='NUMDIM9'  and tbpro.id=a.ps_c_pro_id ),'') as numdim9,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.priceband and tbdim.ad_procolumn_name='NUMDIM3'  and tbpro.id=a.ps_c_pro_id ),'') as numdim3,
           ifnull((select tbdim.ename from ps_c_prodim_item tbdim,ps_c_pro tbpro where tbdim.id=tbpro.priceband and tbdim.ad_procolumn_name='NUMDIM11'  and tbpro.id=a.ps_c_pro_id ),'') as numdim11,
           (select specobj.ecode from ps_c_specobj specobj,ps_c_sku tbsku where specobj.id=tbsku.ps_c_spec1obj_id and tbsku.id=a.id ) as  spec1code, 
           (select specobj.ename from ps_c_specobj specobj,ps_c_sku tbsku where specobj.id=tbsku.ps_c_spec1obj_id and tbsku.id=a.id ) as  spec1name, 
           (select specobj.ecode from ps_c_specobj specobj,ps_c_sku tbsku where specobj.id=tbsku.ps_c_spec2obj_id and tbsku.id=a.id ) as  spec2code, 
           (select specobj.ename from ps_c_specobj specobj,ps_c_sku tbsku where specobj.id=tbsku.ps_c_spec2obj_id and tbsku.id=a.id ) as  spec2name,
           a.ps_c_spec1obj_id as spec1id,a.ps_c_spec2obj_id as spec2id,ifnull(b.fabdesc,'')    
              from ps_c_sku a,ps_c_pro b,cp_c_distrib c,ps_c_brand d  
           where a.sku_ecode='%s' and  a.ps_c_pro_id=b.id 
           and b.cp_c_distrib_id=c.id and b.ps_c_brand_id=d.id  """  # 根据条码查询商品详细信息
    searchSpecObjids = "select ecode,ps_c_specobj_ids from ps_c_pro where ecode='%s' "  # 根据款号查询该款的规格集合和款号
    getSpec1 = "select id,ecode,ename from ps_c_specobj where id in %s order by ecode "  # 根据款号对应的规格实例集合中颜色集合查询对应的颜色实例
    getSpec2 = "select id,ecode,ename,matrixcolno from ps_c_specobj where id in %s order by matrixcolno "  # 根据款号对应的规格实例集合中尺寸集合查询对应的尺寸实例
    getSkusByPro = "select a.id,a.sku_ecode,a.ps_c_spec1obj_id as spec1id,a.ps_c_spec2obj_id as spec2id  from ps_c_sku a,ps_c_pro b  where a.ps_c_pro_id=b.id and b.ecode='%s' "  # 查询某个款号下所有条码信息（款号矩阵框需要）
    getProInfo = "select id,ecode,ename,pricelist from ps_c_pro where ecode like '%s' limit 0,10 "  # 左匹配查询款号信息
    deleteTableRow = "delete from %s where id =%s "  # 增量数据更新时删除对应表格对应ID的数据
    getTableModifiedDate = "select modifieddate from %s where id=%s "  # z查询对应表的对应修改时间字段值

    # VIP
    # {'ENAME': 'selina', 'LASTDATE': None, 'ECODE': '18032900002', 'AVG_TIME_ACTUAL': None, 'ID': '334358385',
    #  'VP_C_VIPTYPE_ENAME': '快鱼普通卡', 'CONSUMER_STORE': None, 'CP_C_STORE_ID': '206598864953', 'AMT_ACTUAL': None,
    #  'ISACTIVE': 'Y', 'RETAIL_INTE': '0', 'NUMBER_CONSUMERS': 0, 'TIMES': None, 'BIRTHDATE': '2006-03-02',
    #  'MOBIL': '13698005414', 'SEX': 'W', 'INTE': '606', 'OPENDATE': 20180329}
    createVipTable = "CREATE TABLE IF NOT EXISTS VIP" \
                     "(ID INT PRIMARY KEY NOT NULL," \
                     "ECODE TEXT," \
                     "VP_C_VIPTYPE_ENAME TEXT," \
                     "ENAME TEXT," \
                     "CP_C_STORE_ID TEXT," \
                     "AVG_TIME_ACTUAL TEXT," \
                     "MOBIL TEXT," \
                     "CONSUMER_STORE TEXT," \
                     "AMT_ACTUAL TEXT," \
                     "SEX TEXT," \
                     "INTE TEXT," \
                     "OPENDATE TEXT," \
                     "LASTDATE TEXT," \
                     "BIRTHDATE TEXT)"

    createVipIndex = "CREATE INDEX VIP_ECODE_MOBIL_STOREID ON VIP(ECODE,MOBIL,CP_C_STORE_ID)"
    getVip = "select * from VIP where (MOBIL = '%s' OR ECODE = '%s') and CP_C_STORE_ID = '%s'"  # 查询VIP
    insertVip = "REPLACE into VIP " \
                "(ID,MOBIL,ECODE,VP_C_VIPTYPE_ENAME," \
                "ENAME,CP_C_STORE_ID,AVG_TIME_ACTUAL,CONSUMER_STORE," \
                "AMT_ACTUAL,SEX,INTE,OPENDATE," \
                "LASTDATE,BIRTHDATE ) " \
                "values (?,?,?,?," \
                "?,?,?,?," \
                "?,?,?,?," \
                "?,?)"

    # 零售表相关
    # 头表
    createRetail = "CREATE TABLE IF NOT EXISTS DL_B_RETAIL(" \
                   "ID integer PRIMARY KEY AUTOINCREMENT NOT NULL," \
                   "BILL_NO TEXT," \
                   "BILL_DATE char(8)," \
                   "CP_C_DISTRIB_ID integer," \
                   "CP_C_DISTRIB_ECODE TEXT," \
                   "CP_C_DISTRIB_ENAME TEXT," \
                   "CP_C_STORE_ID integer," \
                   "CP_C_STORE_ECODE TEXT," \
                   "CP_C_STORE_ENAME TEXT," \
                   "VP_C_VIP_ID INTEGER," \
                   "VP_C_VIP_ECODE TEXT," \
                   "RETAIL_TYPE varchar(20)," \
                   "REMARK TEXT," \
                   "TRANS_STATUS char(1)," \
                   "ORDER_STATUS char(1)," \
                   "IS_HOLD char(1)," \
                   "VIP_SCORE real," \
                   "CONSUME_SCORE real," \
                   "VIP_ADDRESS TEXT," \
                   "VIP_ENAME TEXT," \
                   "VIP_RECEIVER TEXT," \
                   "VIP_TEL TEXT," \
                   "VIP_PRO TEXT," \
                   "VIP_CITY TEXT," \
                   "VIP_DIST TEXT," \
                   "VIP_ADDRESS_REMARK TEXT," \
                   "VIP_EXPRESS_TYPE TEXT," \
                   "VIP_GET_TYPE TEXT," \
                   "STATUS TEXT," \
                   "STATUSID INTEGER," \
                   "STATUSNAME TEXT," \
                   "STATUSENAME TEXT," \
                   "STATUSTIME DATETIME," \
                   "CREATIONDATE DATETIME," \
                   "SUM_QTY_BILL real," \
                   "SUM_AMT_LIST real," \
                   "SUM_AMT_RETAIL real," \
                   "SUM_AMT_RECEIVABLE real," \
                   "SUM_AMT_ACTUAL real," \
                   "SUM_AMT_COST real," \
                   "ACCOUNT real," \
                   "DISCOUNT real)"

    # 明细表
    createRetailItem = "CREATE TABLE IF NOT EXISTS DL_B_RETAIL_ITEM(" \
                       "ID integer PRIMARY KEY AUTOINCREMENT NOT NULL," \
                       "DL_B_RETAIL_ID INTEGER," \
                       "PS_C_PRO_ID INTEGER," \
                       "PS_C_PRO_ECODE varchar(20)," \
                       "PS_C_PRO_ENAME TEXT," \
                       "PRICE_LIST real," \
                       "RETAIL_PRICE real," \
                       "PRICE_RECEIVABLE real," \
                       "PRICE_ACTUAL real," \
                       "PS_C_CLR_ID INTEGER," \
                       "PS_C_CLR_ECODE varchar(20)," \
                       "PS_C_CLR_ENAME varchar(20)," \
                       "PS_C_SIZE_ID INTEGER," \
                       "PS_C_SIZE_ECODE varchar(20)," \
                       "PS_C_SIZE_ENAME varchar(50)," \
                       "PS_C_SKU_ID INTEGER," \
                       "PS_C_SKU_ECODE varchar(20)," \
                       "PS_C_PRO_PROMOTIONTYPE varchar(50)," \
                       "PS_C_PRO_PRONATURE varchar(50)," \
                       "FROM_BILL_ID INTEGER," \
                       "FROM_BILL_NO varchar(20)," \
                       "FROM_BILL_TIEM_ID INTEGER," \
                       "CP_C_SALER_ID INTEGER," \
                       "CP_C_SALER_ECODE varchar(20)," \
                       "CP_C_SALER_ENUMNO varchar(20)," \
                       "CP_C_SALER_ENAME varchar(50)," \
                       "POS_NO varchar(20)," \
                       "RETAIL_TYPE varchar(10)," \
                       "QTY_BILL real," \
                       "QTY_ORDER real," \
                       "AMT_LIST real," \
                       "AMT_RETAIL real," \
                       "AMT_RECEIVABLE real," \
                       "AMT_ACTUAL real," \
                       "VIP_SCORE real," \
                       "CONSUME_SCORE real," \
                       "PROMOTION_ID varchar(200)," \
                       "REMARK varchar(255)," \
                       "CREATIONDATE datetime," \
                       "DISCOUNT real)"

    # 零售付款明细表
    createRetailPayItem = "CREATE TABLE IF NOT EXISTS DL_B_RETAILPAY_ITEM(" \
                          "ID integer PRIMARY KEY AUTOINCREMENT NOT NULL," \
                          "DL_B_RETAIL_ID INTEGER," \
                          "PAY_WAY_ID INTEGER," \
                          "PAY_WAY_CODE varchar(20)," \
                          "PAY_WAY_NAME varchar(50)," \
                          "PAY_AMT real," \
                          "REMARK varchar(255)," \
                          "PAY_ACC varchar(50)," \
                          "PAY_NO varchar(255)," \
                          "COUPON_NO varchar(50)," \
                          "COUPON_AMT real," \
                          "CREATIONDATE datetime)"

    # 获取当天最大的单据号
    getRetailMaxBillNO = "select max(BILL_NO) from DL_B_RETAIL where BILL_DATE = '%s'"

    # {"EMPSINFO": [{"ID": 1, "ECODE": "asda", ...}], "USERSINFO": [{"ID": 1, "ECODE": "asda", ...}]}
    # 营业员表
    createEmpsTable = "CREATE TABLE IF NOT EXISTS EMPS" \
                     "(ID INT PRIMARY KEY NOT NULL," \
                     "ECODE TEXT," \
                     "ENUMNO TEXT," \
                     "ENAME TEXT," \
                     "CP_C_STORE_ID)"
    createEmpsIndex = "CREATE INDEX EMPS_ECODE_ENUMNO_CP_C_STORE_ID ON EMPS(ECODE,ENUMNO,CP_C_STORE_ID)"
    getEmps = "select * from EMPS"  # 查询营业员
    insertEmps = "REPLACE into EMPS " \
                "(ID,ECODE,ENUMNO,ENAME," \
                "CP_C_STORE_ID) " \
                "values (?,?,?,?," \
                "?)"
    deleteEmps = "DELETE FROM EMPS"

    # 用户表
    createUsersTable = "CREATE TABLE IF NOT EXISTS USERS" \
                     "(ID INT PRIMARY KEY NOT NULL," \
                     "ECODE TEXT," \
                     "PASSWORD TEXT," \
                     "ENAME TEXT," \
                     "CP_C_STORE_ID," \
                     "CP_C_DISTRIB_ID)"
    createUsersIndex = "CREATE INDEX USERS_ECODE_CP_C_STORE_ID ON USERS(ECODE,CP_C_STORE_ID)"
    getUsers = "select * from USERS where ECODE = '%s'"  # 查询用户
    usersLogin = "select * from USERS where ECODE = '%s' and PASSWORD= '%s' and CP_C_STORE_ID = '%s'"  #验证用户
    insertUsers = "REPLACE into USERS " \
                "(ID,ECODE,PASSWORD,ENAME," \
                "CP_C_STORE_ID,CP_C_DISTRIB_ID) " \
                "values (?,?,?,?," \
                "?,?)"
    deleteUsers = "DELETE FROM USERS"


if __name__ == '__main__':
    print(SqliteSql.getProInfo % '%rrrr')
    rr = (1, 2, 3)
    print(list(rr))
    tt = list(rr)
    print(tuple(tt))
